﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        public class ComboBoxItem
        {
            public ComboBoxItem(int id, string text)
            {
                ID = id;
                Text = text;
            }

            public int ID { get; private set; }

            public string Text { get; private set; }

            public override string ToString()
            {
                return Text;
            }
        }

        private static string GetDbConnectionString() 
        {
            return ConfigurationManager.AppSettings["DBConnString"];
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
        }

        private void InitializeCategories(SqlConnection connection) {
            using (SqlCommand cmd = connection.CreateCommand()) {
                cmd.CommandText = "SELECT CategoryId, CategoryName FROM Category";
                cmd.CommandType = CommandType.Text;
                using (SqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        int categoryId = reader.GetInt32(0);
                        string categoryName = reader.GetString(1);
                        comboBox1.Items.Add(new ComboBoxItem(categoryId, categoryName));
                    }
                }
            }
        }
        private bool UpdateCategory(int categoryId, string categoryName)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetDbConnectionString()))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "UPDATE Category SET [CategoryName] = @CategoryName" +
                            " WHERE CategoryId = @CategoryId";
                        cmd.CommandType = CommandType.Text;

                        SqlParameter paramCategoryName = cmd.CreateParameter();
                        paramCategoryName.ParameterName = "@CategoryName";
                        paramCategoryName.DbType = DbType.String;
                        paramCategoryName.Value = categoryName;
                        cmd.Parameters.Add(paramCategoryName);

                        SqlParameter paramCategoryID = cmd.CreateParameter();
                        paramCategoryID.ParameterName = "@CategoryId";
                        paramCategoryID.DbType = DbType.Int32;
                        paramCategoryID.Value = categoryId;
                        cmd.Parameters.Add(paramCategoryID);

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                if (ex.Message.Contains("UQ_CategoryName"))
                {
                    MessageBox.Show("Not unique category name. Please, change it and try again.");
                }
                else
                {
                    MessageBox.Show(ex.Message);
                }
                return false;
            }
            return true;
        }

        private int AddNewCategory(string categoryName) 
        {
            int categoryId = -1;
            SqlTransaction tran = null;
            try
            {
                using (SqlConnection conn = new SqlConnection(GetDbConnectionString()))
                {
                    conn.Open();
                    tran = conn.BeginTransaction();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.Transaction = tran;
                        cmd.CommandText = "usp_Category_Insert";
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter paramCategoryName = cmd.CreateParameter();
                        paramCategoryName.ParameterName = "@CategoryName";
                        paramCategoryName.DbType = DbType.String;
                        paramCategoryName.Value = categoryName;
                        cmd.Parameters.Add(paramCategoryName);
                        categoryId = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    tran.Commit();
                }
            }
            catch (SqlException ex)
            {
                if (ex.Message.Contains("UQ_CategoryName"))
                {
                    MessageBox.Show("Not unique category name. Please, change it and try again.");
                }
                else
                {
                    MessageBox.Show(ex.Message);
                }
                if (tran != null) tran.Rollback();
            }
            return categoryId;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetDbConnectionString()))
                {
                    conn.Open();
                    InitializeCategories(conn);
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBoxItem item = comboBox1.SelectedItem as ComboBoxItem;
            if (item != null)
                textBox1.Text = item.Text;
        }

        private void btUpdate_Click(object sender, EventArgs e)
        {
            ComboBoxItem item = comboBox1.SelectedItem as ComboBoxItem;
            if (item != null)
            {
                if (UpdateCategory(item.ID, textBox1.Text))
                {
                    int itemID = item.ID;
                    int itemIndex = comboBox1.SelectedIndex;
                    comboBox1.Items.RemoveAt(itemIndex);
                    comboBox1.Items.Insert(itemIndex, new ComboBoxItem(itemID, textBox1.Text));
                    comboBox1.SelectedIndex = itemIndex;
                }
            }
        }

        private void btAddNew_Click(object sender, EventArgs e)
        {
            int categoryId = AddNewCategory(textBox1.Text);
            if (categoryId != -1)
            {
                comboBox1.Items.Add(new ComboBoxItem(categoryId, textBox1.Text));
                comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
            }
        }
    }
}
